import numpy as np
from win32com.client import Dispatch

#--------------------------------------------------------------------------
#MAIN
if __name__ == "__main__":
    print '============='
    # open excel application module
    xlsApp=Dispatch("Excel.Application")
    filePath = 'C:/_DATA/migration89_08/COUNTY Migration/CO9293/Co9293_excel/Co9293in_excel/'
    totalOutput = np.array([])
    row = 0
    state = ['al', 'ak', 'az', 'ar', 'ca', 'co', 'ct', 'de', 'fl', 'ga', 'hi', 'id', 'il', 'in', 'ia', 'ks', 'ky', 'la', 'me', 'md', 'ma', 'mi', 'mn', 'ms', 'mo', 'mt', 'ne', 'nv', 'nh', 'nj', 'nm', 'ny', 'nc', 'nd', 'oh', 'ok', 'or', 'pa', 'ri', 'sc', 'sd', 'tn', 'tx', 'ut', 'vt', 'va', 'wa', 'wv', 'wi', 'wy']
    #state = ['ak']
    for st in state:
        print st
        inputfile = filePath + 'C9293'+ st +'i.xls'
        xlsBook = xlsApp.Workbooks.Open(inputfile)
        xlsSheet=xlsBook.Sheets[0]
        rs=xlsSheet.UsedRange.Rows
        output = []
        for i in range(9,len(rs)+1):
            #print i
            output.append([xlsSheet.Cells(i,1), xlsSheet.Cells(i,2), xlsSheet.Cells(i,3), xlsSheet.Cells(i,4), xlsSheet.Cells(i,7), xlsSheet.Cells(i,8), xlsSheet.Cells(i,9)])

        totalOutput = np.append(totalOutput, output)
        #print output
        
        outputPath = inputfile[:-4] + 'i.csv'
        np.savetxt(outputPath, output, delimiter=',', fmt = '%s')
    outputPath = inputfile[:-7] + '.csv'
    #totalOutput = np.array(totalOutput)
    totalOutput.shape = (-1, 7)
    np.savetxt(outputPath, totalOutput, delimiter=',', fmt = '%s')
    print 'Done!'
        

